﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using MyLibary.BIZ;

namespace MyLibary.DAL
{
    class DAL_ChiTietNhap
    {
        private static DAL_Connection connect = new DAL_Connection();
        #region Metholds
       /// <summary>
       /// lấy tất cả dữ liệu trong table chi tiết nhập
       /// </summary>
       /// <returns></returns>
        internal static List<BIZ_ChiTietNhap> getAll(string MaHDNhap)
        {
            DataTable tb = connect.ExecuteSelect("SELECT * FROM ChiTietNhap WHERE MaHDNhap='"+MaHDNhap+"'");
            List<BIZ_ChiTietNhap> result = new List<BIZ_ChiTietNhap>();
            for (int i = 0; i < tb.Rows.Count; i++)
                result.Add(new BIZ_ChiTietNhap(tb.Rows[i][0].ToString(), tb.Rows[i][1].ToString(), Convert.ToDecimal(tb.Rows[i][2].ToString()), Convert.ToInt32(tb.Rows[i][3].ToString())));
            return result;
        }
        internal static decimal getTongTien(string MaHDNhap)
        {
            DataTable tb = connect.ExecuteSelect("SELECT SUM(GiaNhap*SoLuong) FROM ChiTietNhap WHERE MaHDNhap='" + MaHDNhap + "'");
            decimal sum = 0;
            try { sum = Convert.ToDecimal(tb.Rows[0][0].ToString()); }
            catch { sum = 0; }
            return sum;
        }
       /// <summary>
       /// lấy chi tiết nhập của 1 sản phẩm trong ngày
       /// </summary>
       /// <param name="MaSanPham"></param>
       /// <param name="date"></param>
       /// <returns></returns>
        internal static List<BIZ_ChiTietNhap> getBySanPham(string MaSanPham,DateTime date)
        {
            string formatSQLDate = String.Format("{0:u}", date);
            formatSQLDate = formatSQLDate.Substring(0, formatSQLDate.Length - 1);
            DataTable tb = connect.ExecuteSelect(String.Format("SELECT MaHDNhap FROM HoaDonNhap WHERE  NgayNhap='{0}' ", formatSQLDate));
            List<BIZ_ChiTietNhap> result = new List<BIZ_ChiTietNhap>();
            for (int i = 0; i < tb.Rows.Count; i++)
            {
                DataTable tb_child = connect.ExecuteSelect(String.Format("SELECT * FROM ChiTietNhap WHERE MaHDNhap='{0}' AND MaSanPham='{1}'", tb.Rows[i][0].ToString(), MaSanPham));
                for(int j=0;j<tb_child.Rows.Count;j++)
                    result.Add(new BIZ_ChiTietNhap(tb_child.Rows[j][0].ToString(), tb_child.Rows[j][1].ToString(), Convert.ToDecimal(tb_child.Rows[j][2].ToString()), Convert.ToInt32(tb_child.Rows[j][3].ToString())));
            }
            return result;
        }
        internal static int getSoLuong(string MaSanPham, DateTime Ngay)
        {
            string formatSQLDate = String.Format("{0:u}", Ngay);
            formatSQLDate = formatSQLDate.Substring(0, formatSQLDate.Length - 1);
            DataTable tb = connect.ExecuteSelect(String.Format("SELECT SUM(SoLuong) FROM ChiTietNhap WHERE MaSanPham='{0}' AND Ngay='{1}'", MaSanPham, formatSQLDate));
            return Convert.ToInt32(tb.Rows[0][0].ToString());
        }
        internal static decimal getThanhTien(string MaSanPham, DateTime Ngay)
        {
            string formatSQLDate = String.Format("{0:u}", Ngay);
            formatSQLDate = formatSQLDate.Substring(0, formatSQLDate.Length - 1);
            DataTable tb = connect.ExecuteSelect(String.Format("SELECT SUM(GiaNhap*SoLuong) FROM ChiTietNhap WHERE MaSanPham='{0}' AND Ngay='{1}'", MaSanPham, formatSQLDate));
            return Convert.ToDecimal(tb.Rows[0][0].ToString());
        }
        internal static bool Insert(BIZ_ChiTietNhap chiTietNhap)
        {
            bool success = connect.ExecuteUpdate(String.Format("INSERT INTO ChiTietNhap VALUES('{0}','{1}','{2}','{3}')", chiTietNhap.MaHDNhap, chiTietNhap.MaSanPham, chiTietNhap.GiaNhap, chiTietNhap.SoLuong));
            if (success)
            {

                BIZ_Kho kho = DAL_Kho.getBySanPham(chiTietNhap.MaSanPham, chiTietNhap.HDNhap.NgayNhap);
                if (kho != null)
                {
                    kho.SoLuongTon += chiTietNhap.SoLuong;
                    kho.Update();
                }
                else
                {
                    kho = new BIZ_Kho(chiTietNhap.MaSanPham, chiTietNhap.HDNhap.NgayNhap, chiTietNhap.SoLuong);
                    kho.Add();
                }
            }
            return success;
        }
        internal static bool Delete(BIZ_ChiTietNhap chiTietNhap)
        {
            bool success = connect.ExecuteUpdate(String.Format("DELETE  ChiTietNhap WHERE MaHDNhap='{0}'", chiTietNhap.MaHDNhap));
            if (success)
            {

                BIZ_Kho kho = DAL_Kho.getBySanPham(chiTietNhap.MaSanPham, chiTietNhap.HDNhap.NgayNhap);
                if (kho != null)
                {
                    kho.SoLuongTon -= chiTietNhap.SoLuong;
                    kho.Update();
                }
            }
            return success;
        }
        internal static bool Update(BIZ_ChiTietNhap chiTietNhap)
        {
            bool success = connect.ExecuteUpdate(String.Format("UPDATE ChiTietNhap SET GiaNhap='{0}',SoLuong='{1}' WHERE MaHDNhap='{2}' AND MaSanPham='{3}'", chiTietNhap.GiaNhap, chiTietNhap.SoLuong, chiTietNhap.MaHDNhap, chiTietNhap.MaSanPham));
            if (success)
            {

                BIZ_Kho kho = DAL_Kho.getBySanPham(chiTietNhap.MaSanPham, chiTietNhap.HDNhap.NgayNhap);
                if (kho != null)
                {
                    kho.SoLuongTon = chiTietNhap.SoLuong;
                    kho.Update();
                }
            }
            return success;
        }
        #endregion
    }
}
